﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.Helper;
using com.nm.library;
using com.nm.library.Helper;
using DistributeAPI.Model;
using NHibernateVertica;
using george.Data;
using george.Helper;
using Vertica.Data.VerticaClient;

namespace DistributeAPI
{
    /// <summary>
    /// 生产订单对应仓库API
    /// </summary>
    public class ProductionOrderWarehouseHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region Insert


        [MustLogin]
        [JsonType]
        [Description("新增")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Insert(
            [ParamDescription("获取传入的数据")]ProductionOrderWarehouseListModel param)
        {
            #region 前端参数验证

            if (string.IsNullOrWhiteSpace(param.CompanyID))
            {
                return Error($"新增失败！公司代码不能为空");
            }
            param.BillNo = param.BillNo.Trim();
            if (string.IsNullOrWhiteSpace(param.BillNo))
            {
                return Error($"新增失败！生产订单不能为空");
            }
            if (param.WarehouseList == null || param.WarehouseList.Count == 0)
            {
                return Error($"新增失败！请至少选择一个虚拟仓");
            }

            #endregion

            #region 检测该是否已存在，避免插入重复数据
            if (AlreadyExists(UserInfo.UserExtrSource, UserInfo.UserCompanyId, param.BillNo))
            {
                return Error($"新增失败！\r\n{param.CompanyID},{param.BillNo}已存在");
            }

            #endregion

            #region 插入新数据
            var list = new List<CommandInfo>();
            foreach (var warehouse in param.WarehouseList)
            {
                string insertSql = $@"
                INSERT
	                INTO
		                ngoods.ngoods_DC_OrderForStock(
			                ExtrSource,
			                CompanyID,
			                BillNo,
			                StockID
		                )
	                VALUES(
		                '{UserInfo.UserExtrSource}',
		                '{UserInfo.UserCompanyId}',
		                '{param.BillNo}',
		                '{warehouse}'
	                )
                ";
                list.Add(new CommandInfo(insertSql, null));
            }

            #endregion

            int n = Helper.ExecuteSqlTran(list);
            if (n > 0)
            {
                return Success(0);
            }
            return Error("插入失败,请重试");
        }
        public bool AlreadyExists(string extrSource, string companyId, string billNo)
        {
            bool result = false;
            try
            {
                string strSql = $@"
SELECT COUNT(CompanyID) from ngoods.ngoods_DC_OrderForStock
WHERE
extrSource='{extrSource}' AND CompanyID='{companyId}' AND
BillNo='{billNo}'
            ";
                var cmd = Helper.GetSqlStringCommand(strSql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                if (count > 0)
                {
                    result = true;
                }
            }
            catch (Exception e)
            {
                throw;
            }

            return result;
        }



        #endregion Insert

        #region Update

        [MustLogin]
        [JsonType]
        [Description("更新")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Update([ParamDescription("获取传入的数据")]ProductionOrderWarehouseListModel param)
        {
            #region 前端参数验证

            if (string.IsNullOrWhiteSpace(param.CompanyID))
            {
                return Error($"更新失败！公司代码不能为空");
            }
            param.BillNo = param.BillNo.Trim();
            if (string.IsNullOrWhiteSpace(param.BillNo))
            {
                return Error($"更新失败！生产订单不能为空");
            }
            if (param.WarehouseList == null || param.WarehouseList.Count == 0)
            {
                return Error($"更新失败！请至少选择一个虚拟仓");
            }

            #endregion

            var list = new List<CommandInfo>();
            int n = 0;

            //更新前删除对应的数据
            string deleteSql = $@"
                                    Delete
                                    FROM ngoods.ngoods_DC_OrderForStock
                                    where ExtrSource='{UserInfo.UserExtrSource}' and  CompanyId='{UserInfo.UserCompanyId}' and BillNo='{param.BillNo}' 
                ";
            list.Add(new CommandInfo(deleteSql, null));
            foreach (var item in param.WarehouseList)
            {
                string insertSql = $@"
                INSERT
	                INTO
		                ngoods.ngoods_DC_OrderForStock(
			                ExtrSource,
			                CompanyID,
			                BillNo,
			                StockID
		                )
	                VALUES(
		                '{UserInfo.UserExtrSource}',
		                '{UserInfo.UserCompanyId}',
		                '{param.BillNo}',
		                '{item}'
	                )
                ";
                list.Add(new CommandInfo(insertSql, null));
                n = Helper.ExecuteSqlTran(list);
            }
            if (n > 0)
            {
                return Success(0);
            }
            return Error("更新失败,请重试");

        }

        #endregion Update

        #region Select

        #region 查询

        #region 获取生产订单对应仓库列表

        [MustLogin]
        [Description("获取生产订单对应仓库列表")]
        [ReturnType(typeof(IList<ProductionOrderWarehouseModel>))]
        public ApiResult GetList(
            [ParamDescription("公司代码")] string CompanyId = "",
            [ParamDescription("仓库ID")] string Warehouse = "",
            [ParamDescription("生产单号")] string BillNo = "",
            [ParamDescription("页码")] int Size = 1,
            [ParamDescription("页数")] int Page = 100)
        {
            var count = ReturnCount(CompanyId, BillNo);
            var result = PaperData<IGrouping<string, ProductionOrderWarehouseModel>>.Create(count, Size, Page);
            string strSql = $@"
     SELECT
	os.CompanyID,
	os.BillNo,
	bs.StockName
from
	ngoods.ngoods_DC_OrderForStock os left
JOIN nods.nods_erp_Bas_Stock bs on
	os.CompanyID = bs.CompanyID
	AND os.StockID = bs.StockID
    and os.ExtrSource=bs.ExtrSource and os.CompanyID=bs.CompanyID
WHERE
	 os.ExtrSource='{UserInfo.UserExtrSource}' and os.CompanyID='{UserInfo.UserCompanyId}' 
	and bs.StockName is NOT NULL
	and os.BillNo in(
		SELECT
			BillNo
		from
			(
				SELECT
					CompanyID,
					BillNo
				FROM
					(
						SELECT
							os.CompanyID,
							os.BillNo,
							bs.StockName
						from
							ngoods.ngoods_DC_OrderForStock os left
						JOIN nods.nods_erp_Bas_Stock bs on
							os.CompanyID = bs.CompanyID
							AND os.StockID = bs.StockID
                            and os.ExtrSource=bs.ExtrSource and os.CompanyID=bs.CompanyID
						WHERE
							bs.StockName is NOT NULL
							{(string.IsNullOrWhiteSpace(CompanyId) ? "" : $" and os.ExtrSource = '{UserInfo.UserExtrSource}' and os.CompanyId = '{CompanyId}' ")}
							{(string.IsNullOrWhiteSpace(Warehouse) ? "" : $" and os.StockID = '{Warehouse}' ")}
							{(string.IsNullOrWhiteSpace(BillNo) ? "" : $" and os.BillNo = '{BillNo}'  ")}
						GROUP by
							os.CompanyID,
							os.BillNo,
							bs.StockName
					) as t
				GROUP BY
					CompanyID,
					BillNo
			) as s
		where
			1 = 1
			and s.CompanyId = '{UserInfo.UserCompanyId}'
		ORDER BY
			CompanyID,
			BillNo 
			 LIMIT {Size} offset {(Page - 1.0) * Size}	
	)
GROUP by
	os.CompanyID,
	os.BillNo,
	bs.StockName
order by
	os.CompanyID,
	os.BillNo,
	bs.StockName
        ";
            var cmd = Helper.GetSqlStringCommand(strSql);
            var dt = Helper.ExecuteDataTable(cmd);
            var list = ModelConvertHelper<ProductionOrderWarehouseModel>.ConvertToModel(dt);
            var groupedList = list.GroupBy(p => p.CompanyID + p.BillNo);
            var warehouseList = new List<ProductionOrderWarehouseModel>();
            foreach (var item in groupedList)
            {
                var temp = new ProductionOrderWarehouseModel();
                int i = 0;
                foreach (var warehouse in item)
                {
                    if (i == 0)
                    {
                        temp.StockId = warehouse.StockId;
                        temp.BillNo = warehouse.BillNo;
                    }
                    temp.StockName += "," + warehouse.StockName;
                    i++;
                }
                if (temp.StockName.Contains(","))
                {
                    temp.StockName = temp.StockName.Substring(1, temp.StockName.Length - 1);
                }
                warehouseList.Add(temp);
            }
            result.Items = warehouseList.GroupBy(p => p.CompanyID + p.BillNo + p.StockId).ToList();
            return Success(result);
        }

        #endregion 获取生产订单对应仓库列表

        #region 获取生产订单对应仓库明细

        [MustLogin]
        [Description("获取生产订单对应仓库明细")]
        [ReturnType(typeof(IList<PlanningOrderQuantityModel>))]
        public ApiResult GetDetails(
            [ParamDescription("公司代码")] string companyId = "",
            [ParamDescription("生产单号")] string billNo = "")
        {

            try
            {
                string strSql = $@"
        SELECT
	os.CompanyID,
	os.BillNo,
	os.StockID
from
	ngoods.ngoods_DC_OrderForStock os 
WHERE
	 os.ExtrSource='{UserInfo.UserExtrSource}' and os.CompanyID='{UserInfo.UserCompanyId}' and 
	os.BillNo='{billNo}'
                ";
                var cmd = Helper.GetSqlStringCommand(strSql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<ProductionOrderWarehouseModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception e)
            {
                throw;
            }
        }

        #endregion 获取生产订单对应仓库明细

        #region 返回行数


        public int ReturnCount(
            [ParamDescription("公司代码")] string CompanyId = "",
            [ParamDescription("生产订单")] string BillNo = "")
        {
            try
            {
                var countSql = string.Empty;
                CompanyId = CompanyId.Trim();
                BillNo = BillNo.Trim();
                #region 查询时获取行数

                countSql = $@"
        SELECT COUNT(CompanyID) from (
SELECT CompanyID,BillNo FROM
(
SELECT
	os.CompanyID,
	os.BillNo,
	bs.StockName
from
	ngoods.ngoods_DC_OrderForStock os left
JOIN nods.nods_erp_Bas_Stock bs on
	os.CompanyID = bs.CompanyID
	AND os.StockID = bs.StockID
    and os.ExtrSource=bs.ExtrSource and os.CompanyID=bs.CompanyID
WHERE
	 os.ExtrSource='{UserInfo.UserExtrSource}' and os.CompanyID='{UserInfo.UserCompanyId}' and 
	bs.StockName is NOT NULL
GROUP by
	os.CompanyID,
	os.BillNo,
	bs.StockName
) as t 
GROUP BY
CompanyID,
BillNo
) as s
        where 1=1
        {(string.IsNullOrWhiteSpace(CompanyId) ? "" : $" and s.CompanyId = '{CompanyId}' ")}
        {(string.IsNullOrWhiteSpace(BillNo) ? "" : $" and s.billno = '{BillNo}' ")}

";
                #endregion 查询时获取行数

                var cmd = Helper.GetSqlStringCommand(countSql);

                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                return count;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        #endregion 返回行数

        #endregion 查询

        #endregion Select

        #region Delete

        #region 删除

        [MustLogin]
        [Description("删除生产订单对应仓库")]
        [ReturnType(typeof(IList<ProductionOrderWarehouseModel>))]
        public ApiResult Remove([ParamDescription("公司代码")] string companyId = "",
            [ParamDescription("生产订单")] string billNo = "")
        {

            try
            {
                string deleteSql = $@"
                                    Delete
                                    FROM ngoods.ngoods_DC_OrderForStock
                                    where ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' and BillNo='{billNo}' 
                ";
                List<CommandInfo> cmdList = new List<CommandInfo>();
                CommandInfo cmd = new CommandInfo { CommandText = deleteSql };
                cmdList.Add(cmd);
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            catch (Exception e)
            {
                throw;
            }
        }



        #endregion 删除

        #endregion Delete
    }
}

